﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyQuanCafe.DataAccess;
using QuanLyQuanCafe.BusinessObject;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace QuanLyQuanCafe.BusinessLogic
{
    class MonAnBL
    {
        KetNoiDuLieu connect = new KetNoiDuLieu();
        public DataTable DSMonAn()
        {
            string sql = "SELECT * FROM MONAN";
            return connect.LayDanhSachDuLieu(sql);
        }

        public DataTable DSMonTheoMa(string value)
        {
            string sql = "SELECT * FROM MONAN WHERE MaMon = '"+value+"'";
            return connect.LayDanhSachDuLieu(sql);
        }


        public int Count()
        {
            string sql = "SELECT * FROM MONAN";
            DataTable table = connect.LayDanhSachDuLieu(sql);
            return table.Rows.Count;
        }

        public DataTable DSMonNhomMon()
        {
            string sql = "SELECT MONAN.MaMon,MONAN.TenMon, MONAN.GiaThuong, NHOMMON.TenNhom FROM MONAN, NHOMMON WHERE NHOMMON.MaNhom = MONAN.MaNhom AND MONAN.MaNhom != '3'";
            return connect.LayDanhSachDuLieu(sql);
        }

        public DataTable DSTenMon(string value)
        {
            string sql = "SELECT MONAN.MaMon, MONAN.TenMon, MONAN.GiaThuong, NHOMMON.TenNhom FROM MONAN, NHOMMON WHERE NHOMMON.MaNhom = MONAN.MaNhom AND NHOMMON.MaNhom = '" + value + "'";
     
            return connect.LayDanhSachDuLieu(sql);
        }

        public int GetMon(string value)
        {
            string sql = "SELECT MONAN.TenMon FROM MONAN, NHOMMON WHERE MONAN.MaNhom = NHOMMON.MaNhom AND NHOMMON.TenNhom = '"+value+"'";
            DataTable table = connect.LayDanhSachDuLieu(sql);
            return table.Rows.Count;
        }

        //VuVD

        public DataTable LayDanhSachTraCuuMonAn()
        {
            string sql = "SELECT MONAN.TenMon, MONAN.GiaThuong, NHOMMON.TenNhom FROM MONAN, NHOMMON WHERE NHOMMON.MaNhom = MONAN.MaNhom AND MONAN.MaNhom != '3'";
            return connect.LayDanhSachDuLieu(sql);
        }

        public DataTable LayKetQuaDanhSachTraCuuMonAn(string tenMA, string giathuongMA, string tennhomMA)
        {
            string sql = string.Format("SELECT MONAN.TenMon, MONAN.GiaThuong, NHOMMON.TenNhom FROM MONAN, NHOMMON WHERE NHOMMON.MaNhom = MONAN.MaNhom AND MONAN.TenMon LIKE N'%{0}%' AND MONAN.GiaThuong LIKE '%{1}%' AND NHOMMON.TenNhom LIKE N'%{2}%' AND MONAN.MaNhom != '3'", tenMA, giathuongMA, tennhomMA);
            return connect.LayDanhSachDuLieu(sql);
        }

        public DataTable LayKetQuaDanhSachTraCuuMonAnTheoTen(string tenMA)
        {
            string sql = string.Format("SELECT MONAN.MaMon, MONAN.TenMon, NHOMMON.TenNhom FROM MONAN, NHOMMON WHERE NHOMMON.MaNhom = MONAN.MaNhom AND MONAN.TenMon LIKE N'{0}'", tenMA);
            return connect.LayDanhSachDuLieu(sql);
        }

        //VuVD

        public bool CheckMonAn(int value)
        {
            if (connect.KiemTraGiaTriInt("MONAN", "MaMon", value))
                return true;
            return false;
        }

        public int FindMa()
        {
            int mamon=1;
            string sql = "SELECT MAMON FROM MONAN";
            
            DataTable monan = connect.LayDanhSachDuLieu(sql);
            if (monan.Rows.Count > 0)
            {
                for (int i = 0; i < monan.Rows.Count; i++)
                {

                    if (mamon == int.Parse(monan.Rows[i]["MaMon"].ToString()))
                        mamon++;
                    else return mamon;
                }
            }
            return mamon;
        }

        public bool ThemMon(MonAnBO monanBO)
        {
            if (TestSave(monanBO))
            {
                string sql = string.Format("INSERT INTO MONAN (MaMon, TenMon, MaNhom, GiaThuong) VALUES ('{0}', N'{1}', '{2}', '{3}')", monanBO.MaMon, monanBO.TenMon, monanBO.MaNhom, monanBO.GiaThuong);
                if (connect.ExecuteQuery(sql))
                {
                    MessageBox.Show("Thêm Thành Công!", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return true;
                }
            }
            return false;
        }

        public bool SuaMon(MonAnBO monanBO)
        {
            if (TestSave(monanBO))
            {
                string sql = string.Format("UPDATE MONAN SET TenMon = N'{0}', Manhom = '{1}', GiaThuong='{2}'" + "WHERE MaMon = '{3}'", monanBO.TenMon, monanBO.MaNhom, monanBO.GiaThuong, monanBO.MaMon);
                if (connect.ExecuteQuery(sql))
                {
                    MessageBox.Show("Sửa Thành Công!", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return true;
                }
            }
            return false;
        }

        public bool XoaMon(string value)
        {
            //string sql = string.Format("DELETE FROM MONAN WHERE MaMon = '" + value + "'");
            string sql = string.Format("UPDATE MONAN SET TenMon = 'Không Còn Bán', MaNhom = '3' WHERE  MaMon = '" + value + "'");
            //string sql2 = string.Format("DELETE FROM CT_MONAN WHERE MaMon = '" + value + "'");
            if (connect.ExecuteQuery(sql)/* && connect.ExecuteQuery(sql2)*/)
            {
                MessageBox.Show("Xóa Thành Công!", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return true;
            }
            return false;
        }

        public bool TestSave(MonAnBO monanBO)
        {
            if (monanBO.MaMon.Equals(""))
            {
                MessageBox.Show("Mã Món Không Hợp Lệ!", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }
            if (monanBO.TenMon.Equals(""))
            {
                MessageBox.Show("Tên Món Không Hợp Lệ!", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }
            if (monanBO.GiaThuong < 1000)
            {
                MessageBox.Show("Giá Món Phải Lớn Hơn 1000 Đ!", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            return true;
        }

    }
}
